Merging Queries to Combine Related Datasets in Power BI

Course: Microsoft Power BI Training

Topic: Merging Queries to Combine Related Datasets

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Merging Queries in Power BI

In Power BI, data often comes from multiple related tables, files, databases, or business systems. Each dataset may contain only part of the information required for reporting. Merging queries allows users to combine related datasets by matching values in one or more columns.

For example, a sales dataset may contain Order ID, Customer ID, Product ID, Quantity, and Sales Amount. A separate customer dataset may contain Customer ID, Customer Name, City, and Region. By merging these queries using Customer ID, you can bring customer details into the sales dataset.

Merging queries is performed in Power Query Editor. The operation works like a database join: it compares selected columns in two queries and identifies matching records based on the chosen join type.

Microsoft's documentation explains that a merge creates a related-table column that can subsequently be expanded to add selected fields to the primary table. Read the Microsoft Learn guide to merging queries.

1.1 What Is a Query?

A query is a set of instructions used to connect to a data source, retrieve data, and apply transformations. In Power Query, each imported worksheet, database table, or transformed dataset can be represented by a query.

Examples of queries include:

• Sales Query: Contains transaction information.
• Customers Query: Contains customer details.
• Products Query: Contains product names, categories, and prices.
• Regions Query: Contains region names and regional managers.
• Employees Query: Contains employee details and department information.

1.2 What Does Merging Queries Mean?

Merging queries means joining two existing queries based on matching values in selected columns. The resulting query retains the columns from the primary table and includes a new structured column containing related rows from the secondary table. You can expand that column to bring selected fields into the primary table.

1.3 Why Is Query Merging Important?

• Combines information stored in separate datasets.
• Enriches transaction tables with descriptive information.
• Supports data cleaning and preparation before modeling.
• Helps identify matching and unmatched records.
• Can reduce the need to maintain manually copied lookup information.
• Supports reporting across multiple business data sources.
• Allows users to create analysis-ready tables from related information.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose of merging queries in Power BI.
• Identify primary and related tables.
• Recognize suitable matching columns and join keys.
• Merge queries using the Power Query Editor interface.
• Understand Merge Queries and Merge Queries as New.
• Apply different join types, including Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti.
• Expand selected columns from a merged table.
• Work with multiple matching columns.
• Understand duplicate-key issues and their effects on row counts.
• Use fuzzy matching when appropriate.
• Write and understand basic M code for merging queries.
• Troubleshoot missing matches, null values, and unexpected results.
• Apply merging techniques in a practical sales reporting project.

3. Understanding Related Datasets

Related datasets are tables that share a common identifier or business attribute. The shared value allows Power Query to identify which records belong together.

3.1 Example of Related Tables

Consider a retail business that maintains two datasets.

Sales Table

Customers Table

Both tables contain Customer ID. This field can be used as the matching key to bring Customer Name and City into the sales data.

3.2 Expected Result After Merging

Customer C101 appears twice in the sales table because the customer has two orders. Both transactions can correctly match the same customer record.

4. Understanding Primary and Related Tables

When merging queries, Power Query uses a primary table and a related table. In the Merge dialog, the first table is the left table and the second table is the right table. Their positions matter because join types determine which rows are retained.

4.1 Primary Table

The primary table is the table into which related information is being merged. For example, Sales can be the primary table when you want to add customer information to transactions.

4.2 Related Table

The related table contains the information you want to match with the primary table. In the sales example, Customers is the related table because it contains customer names and cities.

4.3 Example

5. Understanding Matching Columns and Join Keys

A join key is a column, or a combination of columns, used to match records between tables. Selecting the correct key is one of the most important parts of merging queries.

5.1 Characteristics of a Good Join Key

• It represents the same business concept in both datasets.
• Values are formatted consistently.
• Data types are compatible.
• Leading and trailing spaces are handled correctly.
• Case differences are considered where relevant.
• Unexpected duplicates are identified.
• Null or blank keys are reviewed.

5.2 Column Names Do Not Have to Match

The column names in the two tables do not need to be identical. For example, CustomerID in the Sales table can match Customer_Code in the Customers table if both fields contain the same identifiers.

What matters is that the selected values represent the same key and are compatible in data type and format.

5.3 Example of Matching Columns

5.4 Data Types Must Be Compatible

If one key is stored as text and the other as a number, the merge may fail to match the intended values. Before merging, inspect both columns and correct their types when necessary.

For example, the text value "101" and the numeric value 101 are not necessarily treated as equal during a merge. Convert both fields to compatible types based on the actual business meaning.

6. Merge Queries vs. Append Queries

Merge and Append are two different ways to combine data in Power Query. They solve different problems and should not be used interchangeably.

6.1 Example of Merge

Sales has Customer ID and Sales Amount. Customers has Customer ID and Customer Name. Merge them on Customer ID to add Customer Name to each transaction.

6.2 Example of Append

January Sales and February Sales have similar columns. Append the queries to create a single table containing transactions from both months.

For more details, see Microsoft's guide to combining multiple queries.

7. Opening Power Query Editor

Before merging queries, load the datasets into Power BI Desktop and open Power Query Editor.

7.1 Steps to Import Data

1. Open Power BI Desktop.
2. Select Home from the ribbon.
3. Choose Get Data.
4. Select the required source, such as Excel workbook, CSV, SQL Server, or another supported connector.
5. Locate and select the data source.
6. Choose the required tables or worksheets in the Navigator window.
7. Select Transform Data to open Power Query Editor.

7.2 Check the Queries Pane

Review the Queries pane on the left side of Power Query Editor. Confirm that both datasets have been imported and that the intended queries are available.

Rename the queries with meaningful names, such as Sales and Customers, if necessary. Clear names make merge operations easier to understand and maintain.

8. Step-by-Step Process to Merge Queries

The following example merges a Sales query with a Customers query to bring customer details into sales transactions.

8.1 Step 1: Select the Primary Query

In the Queries pane, select Sales. This will be the primary table because the objective is to enrich each sales transaction with customer information.

8.2 Step 2: Open the Merge Command

1. Go to the Home tab in Power Query Editor.
2. Find the Combine group.
3. Select Merge Queries or open its dropdown.
4. Choose Merge Queries to add the merge operation to the current query, or Merge Queries as New to create a separate result query.

8.3 Step 3: Select the Related Query

In the Merge dialog, select Customers as the second table. The dialog displays previews of the two tables so you can select the appropriate matching columns.

8.4 Step 4: Select the Matching Columns

1. Select Customer ID in the Sales table preview.
2. Select Customer ID in the Customers table preview.
3. Confirm that the selected columns represent the same identifier.
4. Check the match information displayed by Power Query, where available.

8.5 Step 5: Select the Join Kind

Choose the join type according to the records that should be retained. For a sales report that must preserve every transaction, Left Outer is often appropriate.

8.6 Step 6: Confirm the Merge

Select OK. Power Query adds a structured column containing the related rows from the Customers query. The new column may display the word Table in each row that has a corresponding nested result.

8.7 Step 7: Expand the Merged Column

1. Locate the newly created Customers column.
2. Select the expand icon in its column header.
3. Select the fields you want to add, such as Customer Name and City.
4. Clear fields that are unnecessary, such as a repeated Customer ID, if it is not needed.
5. Choose whether to use the original column name as a prefix.
6. Select OK.

The resulting query contains the original sales fields and the selected customer fields. Review the expanded column names and rename them if necessary.

9. Merge Queries vs. Merge Queries as New

Power Query provides two main merge commands. Both can perform a join, but they differ in how the resulting transformation is organized.

9.1 Example of Merge Queries

If Sales should become the final reporting table, select Sales and use Merge Queries. The merge operation becomes part of the Sales query's Applied Steps.

9.2 Example of Merge Queries as New

If you want to preserve the original Sales and Customers queries and create a separate Sales with Customers query, use Merge Queries as New. This can make it easier to maintain the original queries separately from the final result.

10. Understanding Join Types in Power Query

The join kind determines which rows are retained from the left and right tables. Choosing the correct join type is essential because it affects row counts and which unmatched records appear in the result.

Power Query supports six common join kinds: Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti. Microsoft Learn: Join kinds in Power Query.

10.1 Left Outer Join

A Left Outer join keeps every row from the left table and brings in matching rows from the right table. When a left-side row has no match, the expanded right-side fields are null.

Example: Keep every sales transaction and add customer information wherever a match exists.

10.2 Right Outer Join

A Right Outer join keeps every row from the right table and brings in matching rows from the left table. It is useful when the right table must be fully represented in the result.

Example: Keep every customer record from the right table and bring in sales information when matching transactions exist.

10.3 Full Outer Join

A Full Outer join keeps all rows from both tables. Matching records are combined, while unmatched rows from either side remain in the result with null values for fields that have no corresponding record.

Example: Compare sales customer IDs with a customer master list to investigate missing or unmatched records on both sides.

10.4 Inner Join

An Inner join keeps only records that have matching keys in both tables.

Example: Analyze sales transactions only when the customer ID exists in the customer master table.

10.5 Left Anti Join

A Left Anti join returns rows from the left table that have no matching record in the right table.

Example: Identify sales transactions containing customer IDs that do not exist in the customer master table.

10.6 Right Anti Join

A Right Anti join returns rows from the right table that have no matching record in the left table.

Example: Find customer records that do not appear in the sales dataset.

10.7 Join Types Summary

11. Understanding Join Results With an Example

Consider the following two tables.

Orders Table — Left Table

Customers Table — Right Table

11.1 Left Outer Result

All three orders are retained. O003 has no matching customer record, so Customer Name is null after expansion.

11.2 Inner Result

Only orders with matching customer IDs remain.

11.3 Left Anti Result

This result highlights orders that do not match any customer ID in the Customers table.

11.4 Right Anti Result

This result identifies customers who do not have a matching order in the Orders table.

12. Merging Using Multiple Columns

Some datasets do not contain a single column that uniquely identifies a record. In these cases, you can match records using two or more columns.

12.1 Example

Suppose a company stores product inventory by both Product ID and Warehouse ID. A product can exist in several warehouses, so Product ID alone may not identify a unique inventory record.

To match inventory accurately, select Product ID and Warehouse ID in both tables.

12.2 Steps to Select Multiple Matching Columns

1. Open the Merge dialog.
2. Select the first matching column in the left table.
3. Hold Ctrl and select the next matching column.
4. Repeat the selection in the right table.
5. Make sure the selection order matches between both tables.
6. Choose the appropriate join type.
7. Select OK and inspect the result.

The order of the selected columns must correspond between the two tables. For example, if Product ID is selected first and Warehouse ID second in the left table, select them in the same order in the right table.

13. Expanding a Merged Column

After a merge, Power Query typically adds a structured column containing nested tables from the related query. Expanding this column brings selected fields into the main query.

13.1 Why Is Expansion Necessary?

• It exposes the required fields from the related dataset.
• It lets you choose which fields should be included in the final table.
• It prevents unnecessary columns from being added to the result.
• It allows the resulting field names to be organized for reporting.

13.2 Steps to Expand

1. Locate the merged column in the data preview.
2. Select the expand icon in the column header.
3. Select only the columns needed for analysis.
4. Decide whether to keep the original column name as a prefix.
5. Select OK.
6. Review the newly added columns.
7. Rename the fields where necessary.

13.3 Example

Suppose the merged column is named Customers and contains Customer ID, Customer Name, City, Email, and Phone. If the report requires only Customer Name and City, select those two fields when expanding.

This keeps the final table focused and avoids loading unnecessary contact information.

14. Handling Duplicate Keys and One-to-Many Matches

Duplicate keys can significantly affect merge results. Before merging, understand whether the selected key is unique in either table.

14.1 Unique Key in the Related Table

If every Customer ID appears only once in Customers, each sales transaction can match at most one customer record. This is a common lookup pattern.

14.2 Duplicate Keys in the Related Table

If Customer ID appears multiple times in the Customers table, a single sales row may match multiple customer rows. When the merged nested table is expanded, this can create multiple output rows for that sales transaction.

14.3 Example of Row Multiplication

Suppose the Sales table contains one order for customer C101, while the Customers table contains two records for C101.

After merging and expanding, the order may appear twice, once for each matching customer record. If sales amounts are then summed, the total could be overstated.

14.4 How to Prevent Unexpected Duplicates

• Check whether the intended key should be unique in the related table.
• Identify the reason for repeated keys.
• Correct source-data issues when possible.
• Use Remove Duplicates only when the business rule confirms which records should be retained.
• Use additional matching columns when they are part of the real key.
• Aggregate the related data first if the business requirement calls for one row per key.
• Compare row counts and important totals before and after the merge.

15. Handling Nulls, Spaces, and Inconsistent Values

Even when two columns represent the same identifier, formatting differences can prevent records from matching.

15.1 Common Causes of Failed Matches

• Leading or trailing spaces.
• Different data types.
• Inconsistent capitalization in text fields.
• Missing values or null keys.
• Leading zeros in identifiers.
• Different punctuation or formatting.
• Extra characters in imported values.

15.2 Example

15.3 Recommended Cleaning Process

1. Inspect unmatched records.
2. Confirm the intended meaning of each key.
3. Set compatible data types.
4. Trim unnecessary spaces from text keys.
5. Standardize formatting when the business rules allow it.
6. Preserve meaningful leading zeros in codes and identifiers.
7. Repeat the merge and validate the match rate.

Do not remove leading zeros automatically. An identifier such as 00125 may be different from 125 in a business system, even if both look numeric.

16. Understanding Fuzzy Matching

Fuzzy matching allows Power Query to identify approximate matches between text values rather than requiring exact equality. It can help when datasets contain minor spelling or formatting differences.

16.1 Example

16.2 How to Enable Fuzzy Matching

1. Open the Merge dialog.
2. Select the relevant text columns in both tables.
3. Select the Use fuzzy matching to perform the merge option, where available.
4. Open the fuzzy matching options to review the available settings.
5. Adjust the options according to the quality of the source data and the matching objective.
6. Run the merge and inspect the results manually.

16.3 Important Limitations

• Fuzzy matching is intended for text columns.
• It may produce incorrect matches when values are similar but refer to different entities.
• Results should be reviewed before being used in business-critical reports.
• Exact identifiers such as invoice numbers or account IDs should generally use exact matching when possible.

For authoritative details, refer to Microsoft's merge queries documentation.

17. Merging Queries From Different Data Sources

Power Query can merge compatible tabular queries even when they originate from different supported sources. For example, a sales table may come from SQL Server while a customer lookup table comes from Excel.

17.1 Example

17.2 Important Considerations

• Confirm that credentials are configured for each source.
• Review privacy levels when combining sources.
• Check whether transformations can fold to the original source.
• Consider the effect of source performance on refresh duration.
• Validate that the data types and key formats are compatible.
• Ensure that combining sources complies with organizational data policies.

18. Using M Code to Merge Queries

Power Query M is the formula language used to define data retrieval and transformation steps. When you merge queries through the graphical interface, Power Query generates M expressions to represent the operation.

18.1 Example M Code

let
    Sales = #table(
        type table [
            OrderID = text,
            CustomerID = text,
            SalesAmount = number
        ],
        {
            {"O001", "C101", 2500},
            {"O002", "C102", 1800},
            {"O003", "C103", 3200}
        }
    ),
    Customers = #table(
        type table [
            CustomerID = text,
            CustomerName = text,
            City = text
        ],
        {
            {"C101", "Rahul Sharma", "Mumbai"},
            {"C102", "Priya Patel", "Pune"},
            {"C103", "Amit Verma", "Nashik"}
        }
    ),
    MergedQueries = Table.NestedJoin(
        Sales,
        {"CustomerID"},
        Customers,
        {"CustomerID"},
        "Customers",
        JoinKind.LeftOuter
    ),
    ExpandedCustomers = Table.ExpandTableColumn(
        MergedQueries,
        "Customers",
        {"CustomerName", "City"},
        {"CustomerName", "City"}
    )
in
    ExpandedCustomers

18.2 Explanation of the Code

• Sales: Defines a sample sales table.
• Customers: Defines a sample customer lookup table.
• Table.NestedJoin: Matches the CustomerID column in both tables and creates a nested Customers column.
• JoinKind.LeftOuter: Retains every row from the Sales table.
• Table.ExpandTableColumn: Extracts CustomerName and City from the nested table.
• ExpandedCustomers: Represents the final result.
• in: Returns the final query output.

18.3 Other Join Kinds in M

JoinKind.LeftOuter
JoinKind.RightOuter
JoinKind.FullOuter
JoinKind.Inner
JoinKind.LeftAnti
JoinKind.RightAnti

The join kind should be selected according to the records required in the final output. The code above uses an illustrative in-memory example and can be adapted to actual Power Query queries.

19. Viewing Query Dependencies

Query Dependencies helps users understand how queries are related and how data flows between them. It is especially useful when a project contains staging queries, reference queries, and several merge operations.

19.1 Why Query Dependencies Matter

• Shows relationships between source and downstream queries.
• Helps identify which queries depend on others.
• Makes complex data preparation workflows easier to understand.
• Supports troubleshooting when a source or intermediate query changes.

19.2 How to Use Query Dependencies

1. Open Power Query Editor.
2. Go to the View tab.
3. Select Query Dependencies, where available.
4. Review the visual representation of source queries and dependent queries.
5. Identify which query produces the final merged result.

20. Common Problems When Merging Queries

Merging queries can produce unexpected results if the join keys, data types, or join kinds are incorrect. Use a systematic approach to identify the cause.

21. Best Practices for Merging Queries

• Understand the purpose of both datasets before merging.
• Select a matching key that represents the same business concept in each table.
• Ensure that the data types are compatible.
• Clean key columns before performing the merge.
• Choose the join kind based on the required output, not simply the default selection.
• Inspect the match information displayed in the Merge dialog where available.
• Expand only the fields required for the report or model.
• Use meaningful query and column names.
• Check whether the related table has duplicate keys.
• Compare row counts before and after expansion.
• Validate important business totals after merging.
• Use fuzzy matching cautiously and review approximate matches.
• Consider whether a model relationship is more appropriate than physically combining tables.
• Keep a clear record of important transformation steps.

22. When Should You Merge Queries Instead of Creating a Model Relationship?

Merging queries and creating relationships in the Power BI data model are different approaches. The best choice depends on how the data will be used.

22.1 Example of a Model Relationship

A Sales fact table can remain separate from a Customers dimension table. A relationship on Customer ID can allow customer attributes to filter sales measures without physically merging all customer columns into the Sales table.

22.2 Choosing the Right Approach

Use a merge when the business requirement calls for transforming the data into a combined table before loading. Use model relationships when separate tables support a clear analytical model, especially a star schema with fact and dimension tables.

A merge is not automatically better than a relationship. Consider model design, data volume, refresh performance, field requirements, and the behavior expected in report visuals.

23. Practical Project: Combine Sales and Customer Datasets

This project demonstrates how to merge two related datasets and prepare a combined table for reporting.

23.1 Project Scenario

A retail company stores sales transactions in one Excel file and customer information in another. The reporting team needs a dataset that includes transaction amounts, customer names, cities, and regions.

23.2 Sales Dataset

23.3 Customer Dataset

23.4 Step 1: Import Both Datasets

1. Open Power BI Desktop.
2. Import the sales file.
3. Import the customer file.
4. Choose Transform Data.
5. Confirm that Sales and Customers appear in the Queries pane.

23.5 Step 2: Prepare the Keys

• Confirm that Customer ID is a text field in both queries.
• Trim unnecessary spaces if present.
• Check for blank Customer ID values.
• Confirm whether Customer ID should be unique in the Customers query.

23.6 Step 3: Merge the Queries

1. Select the Sales query.
2. Choose Home > Merge Queries.
3. Select Customers as the related table.
4. Select Customer ID in both table previews.
5. Choose Left Outer join.
6. Select OK.

23.7 Step 4: Expand Customer Details

1. Locate the merged Customers column.
2. Select the expand icon.
3. Select Customer Name, City, and Region.
4. Remove the prefix if it makes the column names unnecessarily long.
5. Select OK.

23.8 Step 5: Validate the Combined Table

The expected result from a Left Outer join is:

ORD005 remains because Left Outer retains all sales rows. Customer C105 has no matching record in the customer dataset, so the expanded customer fields are null.

23.9 Step 6: Investigate Unmatched Records

Use a Left Anti join with Sales as the left table and Customers as the right table to identify transactions with missing customer matches. Investigate the missing customer C105 and determine whether the customer record needs to be added or the transaction corrected.

23.10 Step 7: Load the Data

1. Review the Applied Steps.
2. Confirm the expected row count and column names.
3. Check for unexpected duplicates or null values.
4. Select Close & Apply.
5. Wait for Power BI Desktop to apply the query changes.
6. Confirm that the final query appears in the model if Enable Load is turned on.

23.11 Step 8: Create a Report

• Create a card visual for total sales.
• Create a bar chart for sales by city.
• Create a column chart for sales by product.
• Add a region slicer.
• Investigate unmatched transactions before presenting the report as complete.

24. Practical Exercises

Exercise 1: Merge Sales and Products

Use a Sales query containing Product ID and Sales Amount and a Products query containing Product ID, Product Name, and Category.

• Merge using Product ID.
• Choose Left Outer join.
• Expand Product Name and Category.
• Check whether any sales rows have no matching product.

Exercise 2: Identify Unmatched Customer Records

Merge an Orders table with a Customers table using Left Anti join.

• List orders that have no matching customer.
• Count the unmatched records.
• Inspect the key values.
• Suggest a correction for each data-quality issue.

Exercise 3: Compare Two Customer Lists

Use Full Outer join to compare customer lists from two systems.

• Identify matching customers.
• Identify records that appear only in the first dataset.
• Identify records that appear only in the second dataset.
• Review differences in customer IDs and names.

Exercise 4: Use Multiple Join Keys

Merge inventory tables using Product ID and Warehouse ID.

• Select both keys in the correct order.
• Compare the output with the source tables.
• Identify duplicates caused by non-unique key combinations.
• Explain why Product ID alone may be insufficient.

Exercise 5: Investigate Duplicate Keys

Create a customer table containing a repeated Customer ID and merge it with a small sales table.

• Perform a Left Outer merge.
• Expand the customer name field.
• Observe the resulting row count.
• Explain how duplicate keys affect the output.
• Correct the source or change the query according to a defined business rule.

25. Interview Questions and Answers

Q1. What is merging queries in Power BI?

Answer: Merging queries combines related datasets by matching values in one or more columns. It is performed in Power Query Editor and is similar to a join in a relational database.

Q2. What is a join key?

Answer: A join key is a column or combination of columns used to match records between two tables.

Q3. What is the difference between Merge Queries and Merge Queries as New?

Answer: Merge Queries adds the merge operation to the selected query, while Merge Queries as New creates a separate query containing the merge result.

Q4. What is a Left Outer join?

Answer: A Left Outer join retains all rows from the left table and includes matching information from the right table. Unmatched right-side fields become null after expansion.

Q5. What is an Inner join?

Answer: An Inner join returns only records that have matching key values in both tables.

Q6. What is a Full Outer join?

Answer: A Full Outer join retains all rows from both tables, combining matching rows and preserving unmatched rows from either side.

Q7. What is a Left Anti join?

Answer: A Left Anti join returns records from the left table that do not have a matching record in the right table.

Q8. What is a Right Anti join?

Answer: A Right Anti join returns records from the right table that do not have a matching record in the left table.

Q9. Do matching column names need to be identical?

Answer: No. The column names can differ, but the selected columns must represent the same matching key and have compatible values and data types.

Q10. Why do null values appear after a merge?

Answer: Nulls can appear when a row in the retained table has no matching record in the related table. They may also indicate inconsistent formatting, missing keys, or an incorrect join selection.

Q11. Why can a merge increase the number of rows?

Answer: If the related table contains multiple matching rows for a key, expanding the nested table can create multiple rows for one primary-table record.

Q12. What is fuzzy matching?

Answer: Fuzzy matching attempts to match similar text values when they are not exactly identical. It can help with spelling variations but requires careful validation.

Q13. What is the difference between merging and appending queries?

Answer: Merging joins related datasets using matching columns, while appending stacks rows from multiple datasets into one table.

Q14. What is the purpose of expanding a merged column?

Answer: Expansion brings selected fields from the related nested table into the main query as regular columns.

Q15. When might a relationship be preferable to a merge?

Answer: A model relationship may be preferable when the tables should remain separate for analysis, such as fact and dimension tables in a star schema.

26. Quick Revision Checklist

• Understand the purpose of merging queries.
• Identify the primary and related tables.
• Select suitable matching columns.
• Check data types and key formatting.
• Distinguish Merge Queries from Merge Queries as New.
• Understand all six common join types.
• Expand only the required fields from the merged column.
• Recognize the effects of duplicate keys.
• Investigate nulls and unmatched records.
• Use fuzzy matching cautiously.
• Understand the role of Table.NestedJoin and Table.ExpandTableColumn in M.
• Validate row counts and business totals after merging.
• Know when a model relationship may be more appropriate than a merge.

27. Conclusion

Merging queries is an important Power Query technique for combining related datasets before using them in Power BI reports. By matching records through shared keys, users can enrich transaction data, compare datasets, and identify unmatched records.

To merge successfully, select the correct matching columns, choose an appropriate join type, expand only the fields required, and validate the output. Special attention should be given to duplicate keys, inconsistent data types, missing values, and one-to-many matches because they can affect row counts and business totals.

With practice, merging queries helps create clean, reliable datasets and supports better data preparation and reporting workflows.

To continue learning, explore the Microsoft Power BI Training course, register for the JustAcademy course demo, or visit the JustAcademy website.

28. Additional Learning Resources

• Microsoft Learn: Merge Queries Overview
• Microsoft Learn: Power Query Documentation
• Microsoft Learn: Query Folding Basics
• Microsoft Learn: Understand Relationships in Power BI
• Microsoft Support: Combine Multiple Queries